package com.wanmait.spacesys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.spacesys.util.DbHelper;
import com.wanmait.spacesys.vo.Comment;
import com.wanmait.spacesys.vo.Comment2;
import com.wanmait.spacesys.vo.Post;
import com.wanmait.spacesys.vo.Userinfo;

/**
 * 回复评论:数据访问层
 * @author 28298
 *
 */
public class CommentReplyDAO {
	private CommentReplyDAO () {}
	private static CommentReplyDAO commentReplyDAO=new CommentReplyDAO();
	public static CommentReplyDAO getCommentReplyDAO() {
		return commentReplyDAO;
	}

	//统计回复评论的总条数
	public int getCount(Integer userinfoId) {
		int count=0;
		Connection connection=DbHelper.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		String sql="select count(id) from post where enable=1 and userinfo_id=? ";
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, userinfoId);
			resultSet=preparedStatement.executeQuery();
			if(resultSet.next()) {
				count=resultSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return count;
	}
	
	//通过用户id获得用户发布所有的帖子
	public List<Post> findAllById(Integer userinfoId,int pageNum){
		List<Post> posts=new ArrayList<Post>();
		Connection connection=DbHelper.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		String sql="SELECT post.id,post.title,post.pub_time,post.content,post.enable FROM post WHERE post.userinfo_id=? and enable=1 "
				+ "limit "+(pageNum-1)*2+",2";
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, userinfoId);
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()) {
				Post post=new Post();
				post.setId(resultSet.getInt("id"));
				post.setTitle(resultSet.getString("title"));
				post.setPubTime(resultSet.getTimestamp("pub_time"));
				post.setContent(resultSet.getString("content"));
				post.setEnable(resultSet.getBoolean("enable"));
				posts.add(post);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(resultSet, preparedStatement, connection);
		}
		return posts;
				
	}
	
	//通过用户id和帖子id查询帖子详细内容
	public Post myfindAllByPostId(Integer userinfoId,Integer postId){
		Connection connection=DbHelper.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		Post post=null;
		String sql="SELECT post.id,post.title,post.pub_time,post.content,userinfo_id FROM post  WHERE post.userinfo_id=? and post.id=? ";	
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, userinfoId);
			preparedStatement.setInt(2, postId);
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()) {
			    post=new Post();
				post.setId(resultSet.getInt("id"));
				post.setTitle(resultSet.getString("title"));
				post.setPubTime(resultSet.getTimestamp("pub_time"));
				post.setContent(resultSet.getString("content"));
				Userinfo  userinfo=new Userinfo();
				userinfo.setId(resultSet.getInt("userinfo_id"));
				/* userinfo.setUsername(resultSet.getString("username")); */
				post.setUserinfo(userinfo);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(resultSet, preparedStatement, connection);
		}
		return post;
				
	}
	
	//通过用户id和评论帖子的上一级id获得帖子的回复内容
	public List<Comment2> replyToPost(Integer pid,Integer userinfoId){
		List<Comment2> allComment=new ArrayList<Comment2>();
		Connection connection=DbHelper.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		String sql="SELECT comment1.* ,userinfo.username from comment comment1 \r\n" + 
				"LEFT JOIN comment comment2 ON comment1.pid=comment2.id\r\n" + 
				"LEFT JOIN userinfo ON comment2.userinfo_id=userinfo.id \r\n" + 
				"WHERE comment1.pid=? AND comment1.userinfo_id=?";
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, pid);
			preparedStatement.setInt(2, userinfoId);
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()) {
				Comment2 comment=new Comment2();
				comment.setId(resultSet.getInt("id"));
				comment.setCommentTime(resultSet.getTimestamp("comment_time"));
				comment.setContent(resultSet.getString("content"));
				
				Comment2 comment2=new Comment2();
				Userinfo  userinfo=new Userinfo();
				userinfo.setUsername(resultSet.getString("username"));
				comment2.setUserinfo(userinfo);
				comment.setPid(comment2);
				
				comment.setPostId(resultSet.getInt("post_id"));	
			
				allComment.add(comment);
;				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return allComment;
				
	}
	
	//查询别人对我的帖子评论
	public List<Comment> myFindAllPost(Integer postId){
		List<Comment> comments=new ArrayList<Comment>();
		Connection connection=DbHelper.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		String sql="SELECT comment.* ,userinfo.username from comment LEFT JOIN userinfo ON comment.userinfo_id=userinfo.id WHERE post_id=? AND pid IS NULL";
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, postId);
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()) {
				Comment comment=new Comment();
				comment.setId(resultSet.getInt("id"));
				comment.setCommentTime(resultSet.getTimestamp("comment_time"));
				comment.setContent(resultSet.getString("content"));
				comment.setPid(resultSet.getInt("pid"));
				comment.setPostId(resultSet.getInt("post_id"));	
				Userinfo  userinfo=new Userinfo();
				userinfo.setId(resultSet.getInt("userinfo_id"));
				userinfo.setUsername(resultSet.getString("username"));
				comment.setUserinfo(userinfo);
				comments.add(comment);
;				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return comments;
	}
	
	public static void main(String[] args) {
//		List<Post> comments=CommentReplyDAO.getCommentReplyDAO().findAllById(1, 2);
//		comments.forEach(comment->System.out.println(comment.getTitle()+comment.getId()));
		int count=CommentReplyDAO.getCommentReplyDAO().getCount(1);
		System.out.println(count);
	}

}
